package cn.chainco.cts.idaoImpl;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.springframework.dao.DataAccessResourceFailureException;

import cn.chainco.cts.idao.IWorkDao;
import cn.chainco.cts.jdo.Company;
import cn.chainco.cts.jdo.ContainerNo;
import cn.chainco.cts.jdo.Currency;
import cn.chainco.cts.jdo.Employee;
import cn.chainco.cts.jdo.ExcRate;
import cn.chainco.cts.jdo.Tag;
import cn.chainco.cts.jdo.User;
import cn.chainco.cts.jdo.Work;
import cn.chainco.cts.jdo.WorkFee;
import cn.chainco.cts.jdo.WorkItem;
import cn.chainco.cts.jdo.WorkTeam;
import cn.chainco.cts.utils.CommUtils;
import cn.chainco.cts.utils.TimeUtil;
import cn.chainco.cts.utils.query.Page;
import cn.chainco.cts.utils.query.QueryOrder;
import cn.chainco.cts.utils.query.QueryParam;

@SuppressWarnings("unchecked")
public class WorkDaoImpl extends GenericHibernateDaoImpl<Work, Long> implements IWorkDao{

	/**
	 * 获取某人在某家公司的订单的某种状态的总数
	 */
	public int getCountByStatus(Company company, User user, Employee employee,int status){
		try{
			String sql = "select count(*) from cts_work where companyId=" + company.getId() + "  and workingStatus=" + status;
			if( employee.getType() == 0 ){
				if(company.getUser().getId() != user.getId()){
					sql += " and id in(";
					Map<String, String> userMap = new HashMap<String, String>();
					userMap.put("employee.user.userName", user.getUserName());
					List<Object> workIds = getWorkTeamByConditions(userMap, 2);
					if(workIds == null || workIds.size() == 0)
						return 0;
					for(Object obj: workIds){
						sql += String.valueOf(obj) + ",";
					}
					sql += ")";
					sql = sql.replace(",)", ")");
				}
			}else if( employee.getType() == 1 ){
				sql +=" and (client1="+employee.getId()+" or client2="+employee.getId()+")";
			}
			return ((Number)this.getSession().createSQLQuery(sql).uniqueResult()).intValue();
		}catch(Exception e){
			e.printStackTrace();
		}
		return 0;
	}

	/**
	 * Or查询某个人的订单或者是没有or的时候查询
	 */
	public List<Work> getWorkByOrConditions(Company company, User user,Employee employee, Map<String, String> workConditions, Map<String, String> workItemConditions,
		Map<String, String> workTeamConditions, Map<String, String> tagConditions, int pageSize, int pageNumber){
		int row = (pageNumber - 1) * pageSize;
		List<Work> list = new ArrayList<Work>();
		String hql = "from Work w where w.company.id=" + company.getId() + " and w.workingStatus!=6";
		if( employee.getType() == 0 ){
			if(company.getUser().getId() != user.getId()){
				hql += " and w.id in(:user)";
			}
		}else if( employee.getType() == 1 ){
			hql += " and ( w.client1.id="+employee.getId()+" or w.client2.id="+employee.getId()+")";
		}
		if(workConditions != null && workConditions.size() > 0 || workItemConditions != null && workItemConditions.size() > 0 || workTeamConditions != null
			&& workTeamConditions.size() > 0 || tagConditions != null && tagConditions.size() > 0){
			hql += " and(";
			Set<String> key = workConditions.keySet();
			for(Iterator<String> it = key.iterator(); it.hasNext();){
				String s = it.next();
				if(s.equals("item.type") || s.equals("workingStatus")){
					if(workConditions.get("item.type") != null && workConditions.get("item.type").equals("6")){
						hql += " and (w.item.type!=1 and w.item.type!=2 and w.item.type!=3 and w.item.type!=4 and w.item.type!=5)";
					}else{
						hql += " and (w." + s + "='" + workConditions.get(s) + "')";
					}
				}else{
					hql += " or w." + s + " like'%" + workConditions.get(s) + "%'";
				}
			}
			if(getTagByConditions(tagConditions, 1) != null){
				hql += " or w.tags like'%" + getTagByConditions(tagConditions, 1) + "%'";
			}
			if(getWorkItemByConditions(workItemConditions, 1) != null && getWorkItemByConditions(workItemConditions, 1).size() > 0){
				hql += " or w.id in(:workItemConditions)";
			}
			if(getWorkTeamByConditions(workTeamConditions, 1) != null && getWorkTeamByConditions(workTeamConditions, 1).size() > 0){
				hql += " or w.id in(:workTeamConditions)";
			}
			hql += ") order by w.delegateTime desc";
			hql = hql.replace(" and( or", " and(");
			hql = hql.replace(" and( and", " and(");
			// System.out.println( hql );

			Query query = null;
			try{
				query = this.getSession().createQuery(hql);
			}catch(Exception e1){
				e1.printStackTrace();
			}
			if( employee.getType() == 0 ){
				if(company.getUser().getId() != user.getId()){
					Map<String, String> userMap = new HashMap<String, String>();
					userMap.put("employee.user.userName", user.getUserName());
					List<Object> workIds = getWorkTeamByConditions(userMap, 2);
					if(workIds == null || workIds.size() == 0)
						return new ArrayList<Work>();
					query.setParameterList("user", workIds);
				}
			}
			List<Object> list1 = getWorkItemByConditions(workItemConditions, 1);
			if(list1 != null && list1.size() > 0){
				query.setParameterList("workItemConditions", list1);
			}
			List<Object> list2 = getWorkTeamByConditions(workTeamConditions, 1);
			if(list2 != null && list2.size() > 0){
				query.setParameterList("workTeamConditions", list2);
			}
			try{
				list = query.setFirstResult(row).setMaxResults(pageSize).list();
			}catch(Exception e){
				e.printStackTrace();
			}
		}else{
			// System.out.println( hql );
			hql += " order by w.delegateTime desc) ";
			Query query = this.getSession().createQuery(hql);
			if( employee.getType() == 0 ){
				if(company.getUser().getId() != user.getId()){
					Map<String, String> userMap = new HashMap<String, String>();
					userMap.put("employee.user.userName", user.getUserName());
					List<Object> workIds = getWorkTeamByConditions(userMap, 2);
					if(workIds == null || workIds.size() == 0)
						return new ArrayList<Work>();
					query.setParameterList("user", workIds);
				}
			}
			try{
				list = query.setFirstResult(row).setMaxResults(pageSize).list();
			}catch(HibernateException e){
				e.printStackTrace();
			}
		}
		return list;
	}

	/**
	 * And查询某个人的订单
	 */
	public List<Work> getWorkByAndConditions(Company company, User user, Employee employee,Map<String, String> workConditions, Map<String, String> workItemConditions,
		Map<String, String> workTeamConditions, Map<String, String> tagConditions, int pageSize, int pageNumber){
		int row = (pageNumber - 1) * pageSize;
		String hql = "from Work w where w.company.id=" + company.getId() + " and w.workingStatus!=6";
		if( employee.getType() == 0 ){
			if(company.getUser().getId() != user.getId()){
				hql += " and w.id in(:user)";
			}
		}else if( employee.getType() == 1){
			hql += " and ( w.client1.id="+employee.getId()+" or w.client2.id="+employee.getId()+")";
		}
		if(workConditions != null && workConditions.size() > 0){
			hql += " and";
		}
		Set<String> key = workConditions.keySet();
		boolean isHasTime = false;
		Date startTime = null;
		Date endTime = null;
		for(Iterator<String> it = key.iterator(); it.hasNext();){
			String s = it.next();
			if(s.indexOf("|") != -1){
				hql += " and w." + s.replace("|", "") + " like'%" + workConditions.get(s) + "%'";
			}else{
				if("delegateTime".equals(s)){
					isHasTime = true;
					hql += " and w.delegateTime>=(:a) and w.delegateTime<=(:b)";
					SimpleDateFormat simpledateformat = new SimpleDateFormat("yyyy-MM-dd");
					String startTime1 = workConditions.get(s) + "-01";
					String endTime1 = workConditions.get(s) + "-31";
					try{
						startTime = simpledateformat.parse(startTime1);
						endTime = simpledateformat.parse(endTime1);
					}catch(ParseException e){
						e.printStackTrace();
					}
				}else{
					if(workConditions.get("item.type") != null && workConditions.get("item.type").equals("6")){
						hql += " and (w.item.type!=1 and w.item.type!=2 and w.item.type!=3 and w.item.type!=4 and w.item.type!=5)";
					}else{
						hql += " and w." + s + "='" + workConditions.get(s) + "'";
					}
				}
			}

		}
		if(tagConditions != null && tagConditions.size() > 0){
			String tags = getTagByConditions(tagConditions, 2);
			if(tags == null || tags.trim().length() == 0)
				return new ArrayList<Work>();
			if(tags != null && tags.trim().length() > 0){
				String tagResult = getTagByConditions(tagConditions, 2);
				String arr[] = tagResult.split(",");
				hql += " and (";
				String tagSql = "";
				for(int i = 0; i < arr.length; i++){
					if(CommUtils.isCorrectNumber(arr[i])){
						tagSql += " or w.tags like'%," + arr[i] + ",%'";
					}
				}
				hql += tagSql;
				hql = hql.replace(" ( or", " (");
				hql += ")";
			}
		}
		List<Object> list1 = getWorkItemByConditions(workItemConditions, 2);
		if(workItemConditions != null && workItemConditions.size() > 0 && (list1 == null || list1.size() == 0))
			return new ArrayList<Work>();
		if(workItemConditions != null && workItemConditions.size() > 0){
			hql += " and w.id in(:workItemConditions)";
		}
		List<Object> list2 = getWorkTeamByConditions(workTeamConditions, 2);
		if(workTeamConditions != null && workTeamConditions.size() > 0 && (list2 == null || list2.size() == 0))
			return new ArrayList<Work>();
		if(workTeamConditions != null && workTeamConditions.size() > 0){
			hql += " and w.id in(:workTeamConditions)";
		}
		hql += " order by w.delegateTime desc";
		hql = hql.replace(" and and", " and");
		// System.out.println( hql );
		Query query = this.getSession().createQuery(hql);
		if( employee.getType() == 0 ){
			if(company.getUser().getId() != user.getId()){
				Map<String, String> userMap = new HashMap<String, String>();
				userMap.put("employee.user.userName", user.getUserName());
				List<Object> workIds = getWorkTeamByConditions(userMap, 2);
				if(workIds == null || workIds.size() == 0)
					return new ArrayList<Work>();
				query.setParameterList("user", workIds);
			}
		}
		if(workItemConditions != null && workItemConditions.size() > 0){
			query.setParameterList("workItemConditions", list1);
		}
		if(workTeamConditions != null && workTeamConditions.size() > 0){
			query.setParameterList("workTeamConditions", list2);
		}
		if(isHasTime){
			query.setParameter("a", startTime);
			query.setParameter("b", endTime);
		}
		List<Work> listWork = new ArrayList<Work>();
		try{
			listWork = query.setFirstResult(row).setMaxResults(pageSize).list();
		}catch(Exception e){
			e.printStackTrace();
		}
		return listWork;
	}

	/**
	 * 根据条件获取WorkItem,type=1为or查询，type=2为and查询
	 */
	private List<Object> getWorkItemByConditions(Map<String, String> workItemConditions, int type){
		if(workItemConditions == null || workItemConditions.size() == 0)
			return null;
		String hql = "select w.work.id from WorkItem w where";
		Set<String> key = workItemConditions.keySet();
		boolean isHasStartTime = false;
		boolean isHasEndTime = false;
		Date startTime = null;
		Date endTime = null;
		SimpleDateFormat simpledateformat = new SimpleDateFormat("yyyy-MM-dd");
		for(Iterator<String> it = key.iterator(); it.hasNext();){
			String s = it.next();
			if(type == 1){
				hql += " or w." + s + " like'%" + workItemConditions.get(s) + "%'";
			}else if(type == 2){
				if(s.indexOf("|") != -1){
					hql += " and w." + s.replace("|", "") + " like'%" + workItemConditions.get(s) + "%'";
				}else{
					if("startTime".equals(s)){
						isHasStartTime = true;
						hql += " and w.startTime>=(:a)";
						String startTime1 = workItemConditions.get(s);
						try{
							startTime = simpledateformat.parse(startTime1);
						}catch(ParseException e){
							e.printStackTrace();
						}
					}else if("endTime".equals(s)){
						isHasEndTime = true;
						hql += " and w.endTime<=(:b)";
						String endTime1 = workItemConditions.get(s);
						try{
							endTime = simpledateformat.parse(endTime1);
						}catch(ParseException e){
							e.printStackTrace();
						}
					}else{
						hql += " and w." + s + "='" + workItemConditions.get(s) + "'";
					}
				}
			}
		}
		hql = hql.replace("where or", "where");
		hql = hql.replace("where and", "where");
		Query query = this.getSession().createQuery(hql);
		if(isHasStartTime){
			query.setParameter("a", startTime);
		}
		if(isHasEndTime){
			query.setParameter("b", endTime);
		}
		List<Object> list = query.list();
		return list;
	}

	/**
	 * 根据条件获取WorkTeam,type=1为or查询，type=2为and查询
	 */
	private List<Object> getWorkTeamByConditions(Map<String, String> workTeamConditions, int type){
		if(workTeamConditions == null || workTeamConditions.size() == 0)
			return null;
		try{
			String hql = "select w.work.id from WorkTeam w where";
			Set<String> key = workTeamConditions.keySet();
			for(Iterator<String> it = key.iterator(); it.hasNext();){
				String s = it.next();
				if(type == 1){
					hql += " or w." + s + " like'%" + workTeamConditions.get(s) + "%'";
				}else if(type == 2){
					if(s.indexOf("|") != -1){
						hql += " and w." + s.replace("|", "") + " like'%" + workTeamConditions.get(s) + "%'";
					}else{
						hql += " and w." + s + "='" + workTeamConditions.get(s) + "'";
					}
				}
			}
			hql = hql.replace("where or", "where");
			hql = hql.replace("where and", "where");
			List<Object> list = this.getSession().createQuery(hql).list();
			return list;
		}catch(Exception e){
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 根据条件获取Tag,type=1为or查询，type=2为and查询
	 */
	private String getTagByConditions(Map<String, String> tagConditions, int type){
		if(tagConditions == null || tagConditions.size() == 0)
			return null;
		String hql = "from Tag t where";
		Set<String> key = tagConditions.keySet();
		for(Iterator<String> it = key.iterator(); it.hasNext();){
			String s = it.next();
			if(type == 1){
				hql += " or t." + s + " like'%" + tagConditions.get(s) + "%'";
			}else if(type == 2){
				if(s.indexOf("|") != -1){
					hql += " and t." + s.replace("|", "") + " like'%" + tagConditions.get(s) + "%'";
				}else{
					hql += " and t." + s + "='" + tagConditions.get(s) + "'";
				}
			}
		}
		hql = hql.replace("where or", "where ");
		hql = hql.replace("where and", "where ");
		List<Tag> tags = this.getSession().createQuery(hql).list();
		if(tags == null || tags.size() == 0)
			return null;
		String result = ",";
		for(Tag obj: tags){
			result += obj.getId() + ",";
		}
		return result;
	}

	/**
	 * 根据work获取不同类型的费用
	 */
	public List<WorkFee> getFeesByWork(Work work, int type){
		String hql = "from WorkFee where work=? and type=?";
		Query query = this.getSession().createQuery(hql);
		query.setParameter(0, work);
		query.setParameter(1, type);
		return query.list();
	}

	/**
	 * 根据work获取其他委托项目
	 */
	public List<WorkItem> getItemsByWork(Work work){
		String hql = "from WorkItem w where w.work=?";
		Query query = this.getSession().createQuery(hql);
		query.setParameter(0, work);
		return query.list();
	}

	/**
	 * 获取某家公司指定创建月份的订单总数
	 */
	public int getCountByCompany(Company company, Date date){
		String sql = "select count(*) from cts_work t where companyId=? and date_format(createTime,'%Y-%m')=date_format(?,'%Y-%m')";
		return ((Number)this.getSession().createSQLQuery(sql).setParameter(0, company.getId()).setParameter(1, TimeUtil.getYYYY_MM_DD(date)).uniqueResult()).intValue();
	}

	/**
	 * 根据订单号查询订单
	 */
	public Work getWorkByWorkCode(Company company, String workCode){
		String sql = "select t from Work t where t.company.id=? and t.workCode=?";
		List<Work> list = this.getSession().createQuery(sql).setParameter(0, company.getId()).setParameter(1, workCode).list();
		if(list.size() > 0){
			Work work = list.get(0);
			return work;
		}
		return null;
	}

	/**
	 * 添加箱量柜号
	 * @param map key是类型，value是数量
	 * @param work
	 */
	public void addBoxAmount(Map<Integer, Integer> map, Work work){
		for(Integer key: map.keySet()){
			for(int i = 0; i < map.get(key); i++){
				ContainerNo cn = new ContainerNo();
				cn.setWork(work);
				cn.setTypeAndSize(key.toString());
				this.getSession().save(cn);
			}
		}
	}

	/**
	 * 统计箱量类型数量
	 * @param work key是类型，value是数量
	 * @return
	 */
	public Map<Integer, Integer> getBoxTypeCount(Work work){
		String hql = "select t.typeAndSize, count(t.typeAndSize) from ContainerNo t where work = ? group by t.typeAndSize";
		List<Object[]> list = this.getSession().createQuery(hql).setParameter(0, work).list();
		Map<Integer, Integer> map = new HashMap<Integer, Integer>();
		for(int i = 0; i < list.size(); i++){
			try{
				map.put(Integer.parseInt(list.get(i)[0].toString()), Integer.parseInt(list.get(i)[1].toString()));
			}catch(NumberFormatException e){
				e.printStackTrace();
			}
		}
		return map;
	}

	/**
	 * 获取订单全部箱量
	 */
	public List<ContainerNo> getAllContainerNoByWork(Work work){
		String hql = "from ContainerNo t where t.work = ? order by CAST(t.typeAndSize as int)";
		return this.getSession().createQuery(hql).setParameter(0, work).list();
	}

	/**
	 * 删除count条空的柜号
	 * @return 返回删除的条数
	 */
	public Integer delBoxByEmpty(Work work, String boxType, int count){
		String sql = "delete from cts_containerno where (cntrNo is null or cntrNo = '') and workId = ? and typeAndSize = ? limit ?";
		try{
			return this.getSession().createSQLQuery(sql).setParameter(0, work.getId()).setParameter(1, boxType).setParameter(2, count).executeUpdate();
		}catch(DataAccessResourceFailureException e){
			e.printStackTrace();
		}catch(HibernateException e){
			e.printStackTrace();
		}catch(IllegalStateException e){
			e.printStackTrace();
		}
		return 0;
	}

	/**
	 * 根据地点类型查询其它委托项目，地点类型为null查询所有
	 */
	public List<WorkItem> getWorkItemByType(Work work, Integer type){
		String hql = "from WorkItem where work = ?";
		if(type != null){
			hql += " and type = " + type;
		}
		return this.getSession().createQuery(hql).setParameter(0, work).list();
	}

	/**
	 * 根据单查询整个单的委托项目完成状态，同时更加结果算出单本身的状态
	 */
	public void updateWorkStatus(Work work){
		String sql = "select count(*) from cts_workitem where status = 1 and workId = " + work.getId();
		Integer count = Integer.parseInt(this.getSession().createSQLQuery(sql).uniqueResult().toString());
		if(count != null && count > 0){
			String updateSql = "update cts_work set workingStatus=4 where id=" + work.getId();
			this.getSession().createSQLQuery(updateSql).executeUpdate();
			return;
		}else{
			String updateSql = "update cts_work set workingStatus=1 where id=" + work.getId();
			this.getSession().createSQLQuery(updateSql).executeUpdate();
		}

		sql = "select max(endTime) from cts_workitem where workId=" + work.getId();
		Date date = (Date)(this.getSession().createSQLQuery(sql).uniqueResult());
		if(date == null)
			return;
		if(TimeUtil.dateIsPass(date)){
			String updateSql = "update cts_work set workingStatus=3 where id=" + work.getId();
			this.getSession().createSQLQuery(updateSql).executeUpdate();
			return;
		}
		String sql2 = "select min(startTime) from cts_workitem where workId=" + work.getId();
		Date date2 = (Date)(this.getSession().createSQLQuery(sql2).uniqueResult());
		if(TimeUtil.dateIsPass(date2)){
			String updateSql2 = "update cts_work set workingStatus=2 where id=" + work.getId();
			this.getSession().createSQLQuery(updateSql2).executeUpdate();
			return;
		}
	}

	/**
	 * 获取某人在某家公司的订单的某种单的总数
	 */
	public int getCountByType(Company company, User user,Employee employee, String itemName){
		try{
			String sql = "";
			if(!itemName.equals("other")){
				sql = "select count(*) from cts_work where companyId=" + company.getId() + " and workingStatus!=6  and itemId in(select id from cts_item where name_en='" + itemName + "')";
			}else{
				sql = "select count(*) from cts_work where companyId=" + company.getId()
					+ "  and itemId in(select id from cts_item where name_en not in('FCL','LCL','AIR','EXPRESS','TRUCKING'))";
			}
			if( employee.getType() == 0 ){
				if(company.getUser().getId() != user.getId()){
					sql += " and id in(";
					Map<String, String> userMap = new HashMap<String, String>();
					userMap.put("employee.user.userName", user.getUserName());
					List<Object> workIds = getWorkTeamByConditions(userMap, 2);
					if(workIds == null || workIds.size() == 0)
						return 0;
					for(Object obj: workIds){
						sql += String.valueOf(obj) + ",";
					}
					sql += ")";
					sql = sql.replace(",)", ")");
				}
			}else if( employee.getType() == 1 ){
				sql +=" and (client1="+employee.getId()+" or client2="+employee.getId()+")";
			}
			return ((Number)this.getSession().createSQLQuery(sql).uniqueResult()).intValue();
		}catch(Exception e){
			e.printStackTrace();
		}
		return 0;
	}
	/**
	 * 统计订单
	 * statisticalType ：统计类型 1-统计奖励,2-统计毛利,3-统计订单
	 * conditions : 条件，统计结束后会多一个Key为workTotal的总数量
	 */
	public List<Work> statisticalWorks(Company company, int statisticalType, boolean isGroup, Map<String, Object> conditions, int pageSize, int pageNumber) {
		List<Object> params = new ArrayList<Object>();
		StringBuffer whereStr = new StringBuffer();
		params.add(company.getId());
		String seleStr = null, gByStr = null, oByStr = null;
		
		if(statisticalType == 4){ //统计员工
			whereStr.append("from WorkTeam t where t.work.company.id=? and t.auth <> 2 ");
			seleStr = "select t.work,t.employee,count(t.work.item) ";
			gByStr = " DATE_FORMAT(t.work.delegateTime, '%Y-%m' ), t.employee.id, t.work.item.id";
			oByStr = " t.employee.id, DATE_FORMAT(t.work.delegateTime, '%Y-%m' ) desc, w.item.id";
			if(conditions.get("employeeId") != null){
				whereStr.append(" and t.employee.id = ? ");
				params.add(Long.parseLong(conditions.get("employeeId").toString()));
			}
		}else if(statisticalType == 5) { //统计服务商
			whereStr.append("from WorkItem t where t.id in (select t2.id from WorkItem t2 where t2.work.company.id=? group by t2.work.id, t2.service.id) ");
			seleStr = "select t.work,t.service,count(t.work.item) ";
			gByStr = " DATE_FORMAT(t.work.delegateTime, '%Y-%m' ), t.service.id, t.work.item.id";
			oByStr = " t.service.id, DATE_FORMAT(t.work.delegateTime, '%Y-%m' ) desc, w.item.id";
			if(conditions.get("employeeId") != null){
				whereStr.append(" and t.service.id = ? ");
				params.add(Long.parseLong(conditions.get("employeeId").toString()));
			}
		}else{ 
			whereStr.append("from Work w,Employee e where w.company.id=? and e.company.id=? and w.client1.id = e.id");
			params.add(company.getId());
			seleStr = "select w,e,count(w.item) ";
			gByStr = " month(w.delegateTime), e.id";
			oByStr = " w.delegateTime desc";
			if(statisticalType == 3){ //统计委托人
				gByStr = " DATE_FORMAT(w.delegateTime, '%Y-%m' ), e.id, w.item.id";
				oByStr = " e.id, DATE_FORMAT(w.delegateTime, '%Y-%m' ) desc, w.item.id";
			}
			if(conditions.get("employeeId") != null){
				whereStr.append(" and e.id = ? ");
				params.add(Long.parseLong(conditions.get("employeeId").toString()));
			}
		}
		
		if(conditions.get("year") != null){
			whereStr.append(" and year(w.delegateTime) = ? ");
			params.add(Integer.parseInt(conditions.get("year").toString()));
		}else if(conditions.get("month") == null && conditions.get("minMonth") != null && conditions.get("maxMonth") != null){
			whereStr.append(" and year(w.delegateTime) = ? ");
			params.add(Integer.parseInt(TimeUtil.getYY(new Date())));
		}
		if(conditions.get("month") != null){
			whereStr.append(" and DATE_FORMAT(w.delegateTime, '%Y-%m' ) = ? ");
			params.add(conditions.get("month").toString());
		}
		if(conditions.get("minMonth") != null){
			whereStr.append(" and DATE_FORMAT(w.delegateTime, '%Y-%m' ) >= ? ");
			params.add(conditions.get("minMonth").toString());
		}
		if(conditions.get("maxMonth") != null){
			whereStr.append(" and DATE_FORMAT(w.delegateTime, '%Y-%m' ) <= ? ");
			params.add(conditions.get("maxMonth").toString());
		}
		if(conditions.get("itemType") != null){
			whereStr.append(" and w.item.id = ? ");
			params.add(Long.parseLong(conditions.get("itemType").toString()));
		}
		if(conditions.get("teamId") != null){
			whereStr.append(" and w.id in (select wt.work.id from WorkTeam wt where wt.employee.id=?) ");
			params.add(Long.parseLong(conditions.get("teamId").toString()));
		}
		if(conditions.get("tag") != null){
			String sql = "from Tag t where t.company.id=? and t.name like '%"+conditions.get("tag").toString().trim()+"%'";
			List<Tag> tagList = this.getSession().createQuery(sql).setParameter(0, company.getId()).list();
			if(tagList.size() > 0){
				whereStr.append(" and (");
				for (int i = 0; i < tagList.size(); i++) {
					whereStr.append(" w.tags like '%,"+tagList.get(i).getId()+",%' ");
					if(i < tagList.size() - 1){
						whereStr.append(" or ");
					}
				}
				whereStr.append(" )");
			}else{
				return new ArrayList<Work>();
			}
		}
		if(statisticalType == 1){
			StringBuffer sql = new StringBuffer("select f.work.id from WorkFee f where type = 2 ");
			if(conditions.get("rewardId") != null){
				sql.append(" and f.employee1.id=? ");
				params.add(Long.parseLong(conditions.get("rewardId").toString()));
			}
			if("1".equals(conditions.get("status1"))){
				sql.append(" and f.rewardTime is null ");
			}
			whereStr.append(" and w.id in (" + sql + ")");
		}
		if("1".equals(conditions.get("status2"))){
			whereStr.append(" and w.workingStatus = 5 ");
		}
		if("1".equals(conditions.get("status3"))){
			whereStr.append(" and w.receiveVerifyStatus = 3 ");
		}
		if("1".equals(conditions.get("status4"))){
			whereStr.append(" and w.payVerifyStatus = 3 ");
		}
		String hql = "select count(w.id) " + whereStr.toString();
		List<Work> list = new ArrayList<Work>();
		try {
			Query query = null;
			if(isGroup){
				if(statisticalType > 3){
					hql = hql.replace("w.", "t.work.").replace(" w ", " t.work ");
				}
//				System.out.println(hql);
				query = this.getSession().createQuery(hql);
				for(int i = 0; i<params.size(); i++){
					query.setParameter(i, params.get(i));
				}
				Integer count = ((Number)query.uniqueResult()).intValue();
				conditions.put("workTotal", count);
			}
			if(!isGroup){ //不分组
				hql = "select w " + whereStr.toString() + " order by w.delegateTime desc";
				if(statisticalType > 3){
					hql = hql.replace("w.", "t.work.").replace(" w ", " t ");
				}
//				System.out.println(hql);
				query = this.getSession().createQuery(hql);
				for(int i = 0; i<params.size(); i++){
					query.setParameter(i, params.get(i));
				}
				if(statisticalType == 4){
					List<WorkTeam> teamList = query.list();
					for (WorkTeam team : teamList) {
						team.getWork().setOption1(team.getAuth().toString());
						list.add(team.getWork());
					}
				}else if(statisticalType == 5){
					List<WorkItem> itemList = query.list();
					for (WorkItem item : itemList) {
						String time = TimeUtil.getMM_DDHHMM(item.getStartTime()) + " <> " +TimeUtil.getMM_DDHHMM(item.getEndTime());
						item.getWork().setOption1(time.replace("-", "/").replace("<>", "-"));
						list.add(item.getWork());
					}
				}else{
					list = query.list();
				}
				return list;
			}else{
				hql = seleStr + whereStr.toString() + " group by " + gByStr + " order by " + oByStr;
				if(statisticalType > 3){
					hql = hql.replace("w.", "t.work.").replace(" w ", " t.work ");
				}
//				System.out.println(hql);
				query = this.getSession().createQuery(hql);
				for(int i = 0; i<params.size(); i++){
					query.setParameter(i, params.get(i));
				}
				List<Object[]> oList = query.list();
				for(int i=0; i<oList.size(); i++){
					Object[] obj = oList.get(i);
					Work work = new Work();
					CommUtils.copyProperties(work, obj[0]);
					Employee employee = (Employee)obj[1];
					work.setEmployee(employee);
					work.setOption1(obj[2].toString());
					list.add(work);
				}
			}
		} catch (DataAccessResourceFailureException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (HibernateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalStateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * <pre>
	 * 统计订单费用，返回float数组，保留二位小数
	 * float[0]为应收费用合计，float[1]为应付费用合计，float[2]为奖励合计，float[3]为毛利润，float[4]为公司利润
	 * 
	 */
	public float[] countWorkFees(Work work, Currency currency){
		float[] result = new float[5];
		Map<Currency, Float> map = new HashMap<Currency, Float>();
		map.put(this.getHibernateTemplate().get(Currency.class, 1L), 100f);
		String sql = " select * from m68_excrate group by currencyId order by effectiveDate desc ";
		List<ExcRate> list = this.getSession().createSQLQuery(sql).addEntity(ExcRate.class).list();
		for(ExcRate rate: list){
			map.put(rate.getCurrency(), rate.getToRMB());
		}
		Float rate1 = map.get(currency);
		String hql = " from WorkFee t where t.work = ? ";
		List<WorkFee> workFees = this.getSession().createQuery(hql).setParameter(0, work).list();
		Float receiveCount = 0f;
		Float payCount = 0f;
		Float awardCount = 0f;
		for(WorkFee fee: workFees){
			if(fee.getType() != null){
				if(fee.getType() == 0){
					try {
						receiveCount += fee.getMoney() != null ? fee.getMoney() / rate1 * map.get(fee.getCurrency()) : 0f;
					} catch (RuntimeException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}else if(fee.getType() == 1){
					payCount += fee.getMoney() != null ? fee.getMoney() / rate1 * map.get(fee.getCurrency()) : 0f;
				}
			}
		}
		for(WorkFee fee: workFees){
			if(fee.getType() != null && fee.getType() == 2){
				awardCount += fee.getQuotaReward() != null ? fee.getQuotaReward() / rate1 * map.get(fee.getCurrency()) : 0f;
				if(receiveCount - payCount > 0){
					awardCount += fee.getRatioReward() != null ? (receiveCount - payCount) * fee.getRatioReward() / 100 : 0f;
				}
			}
		}
		result[0] = (float)(Math.round(receiveCount * 100)) / 100;
		result[1] = (float)(Math.round(payCount * 100)) / 100;
		result[2] = (float)(Math.round(awardCount * 100)) / 100;
		result[3] = (float)(Math.round((result[0] - result[1]) * 100)) / 100;
		result[4] = (float)(Math.round((result[3] - result[2]) * 100)) / 100;
		return result;
	}
	/**
	 * 统计奖励费用A奖励+B奖励,并保存到字段totalReward
	 */
	public float countWorkFee(WorkFee workFee, Currency currency){
		Work work = workFee.getWork();
		Map<Currency, Float> map = new HashMap<Currency, Float>();
		map.put(this.getHibernateTemplate().get(Currency.class, 1L), 100f);
		String sql = " select * from m68_excrate group by currencyId order by effectiveDate desc ";
		List<ExcRate> list = this.getSession().createSQLQuery(sql).addEntity(ExcRate.class).list();
		for(ExcRate rate: list){
			map.put(rate.getCurrency(), rate.getToRMB());
		}
		Float rate1 = map.get(currency);
		String hql = " from WorkFee t where t.work = ? ";
		List<WorkFee> workFees = this.getSession().createQuery(hql).setParameter(0, work).list();
		Float receiveCount = 0f;
		Float payCount = 0f;
		Float awardCount = 0f;
		for(WorkFee fee: workFees){
			if(fee.getType() != null){
				if(fee.getType() == 0){
					receiveCount += fee.getMoney() != null ? fee.getMoney() / rate1 * map.get(fee.getCurrency()) : 0f;
				}else if(fee.getType() == 1){
					payCount += fee.getMoney() != null ? fee.getMoney() / rate1 * map.get(fee.getCurrency()) : 0f;
				}
			}
		}
		if(workFee.getType() != null && workFee.getType() == 2){
			awardCount += workFee.getQuotaReward() != null ? workFee.getQuotaReward() / rate1 * map.get(workFee.getCurrency()) : 0f;
			if(receiveCount - payCount > 0){
				awardCount += workFee.getRatioReward() != null ? (receiveCount - payCount) * workFee.getRatioReward() / 100 : 0f;
			}
		}
		float f = (float)(Math.round(awardCount * rate1 / map.get(workFee.getCurrency()) * 100)) / 100;
		workFee.setTotalReward(f);
		if( work.getRewardVerifyStatus()==1 ){
			String updateSql = "update cts_workfee set notWrittenOffmoney="+f +",money="+f+" where id="+workFee.getId();
			this.getSession().createSQLQuery( updateSql ).executeUpdate();
		}
		return (float)(Math.round(awardCount * 100)) / 100;
	}
	
	/**
	 * 财务统计(应收未收应付未付)
	 * conditions : 条件，统计结束后会多一个Key为workCount的总数量
	 * statisticsType=0应收未收, statisticsType=1应付未付
	 */
	public List<WorkFee> statisticsFinancial(Company company, int statisticsType, String clientId, String months, String days, 
		String employeeId, String tag, String workingStatus, int pageSize, int pageNumber){
		StringBuilder hql = new StringBuilder();
		hql.append(" select t.* from cts_workfee t ");
		hql.append(" inner join cts_work w on t.workId = w.id and w.companyId = " + company.getId());
		if(CommUtils.isValidStr(months) && months.indexOf("-") != -1){
			String time1 = months + "-01 00:00:00";
			String[] temp = months.split("-");
			int day = TimeUtil.getDaysInMonth(Integer.parseInt(temp[0]), Integer.parseInt(temp[1]));
			String time2 = months + "-" + day + " 23:59:59";
			hql.append(" and w.delegateTime >= '" + time1 + "'");
			hql.append(" and w.delegateTime <= '" + time2 + "'");
		}
		if("5".equals(workingStatus)){
			hql.append(" and w.workingStatus = 5 ");
		}
		if(CommUtils.isCorrectInt(days)){
			hql.append(" and TO_DAYS(NOW()) - TO_DAYS(w.delegateTime) > " + Integer.parseInt(days));
		}
		if(CommUtils.isValidStr(tag)){
			String sql = " from Tag tag where tag.company.id = ? and tag.name like '%" + tag + "%' ";
			List<Tag> tagList = this.getSession().createQuery(sql).setParameter(0, company.getId()).list();
			if(tagList != null && tagList.size() > 0){
				hql.append(" and ( ");
				for (int i = 0; i < tagList.size(); i++) {
					if(i != 0){
						hql.append(" or ");
					}
					hql.append(" w.tags like '%,"+tagList.get(i).getId()+",%' ");
				}
				hql.append(" )");
			}else{
				return new ArrayList<WorkFee>();
			}
		}
		if(CommUtils.isCorrectInt(employeeId) && !"0".equals(employeeId)){
			hql.append(" inner join cts_workteam wt on wt.workId = w.id and wt.employeeId = " + Long.parseLong(employeeId));
		}
		hql.append(" where t.type = " + statisticsType);
		if(CommUtils.isCorrectInt(clientId) && !"0".equals(clientId)){
			hql.append(" and t.employeeId1 = " + Long.parseLong(clientId));
		}
		hql.append(" and t.money <> t.writtenOffmoney ");
		return this.getSession().createSQLQuery(hql.toString()).addEntity(WorkFee.class).list();
	}
	
	
	/***
	 * 根据传入的work更新最新的和钱有关的状态并且更新其有比例奖励的数据的
	 */
	public void updateWorkFeesStatus( long workId ){
		//根据workId查询应收款
		String hql = "from WorkFee where work.id="+workId+" and type=0";
		List<WorkFee> list = this.getSession().createQuery( hql ).list();
		
		//根据workId查询应付款
		String hql2 = "from WorkFee where work.id="+workId+" and type=1";
		List<WorkFee> list2 = this.getSession().createQuery( hql2 ).list();
		
		//根据workId查询奖励款
		String hql3 = "from WorkFee where work.id="+workId+" and type=2";
		List<WorkFee> list3 = this.getSession().createQuery( hql3 ).list();
		
		String updateSql = "";
		
		int finish = 0;
		//更新奖励同时更新奖励核销状态
		for( WorkFee obj: list3 ){
			countWorkFee( obj, obj.getCurrency() );
			if( obj.getWrittenOffmoney()!=null && obj.getWrittenOffmoney() >0 && obj.getWrittenOffmoney()<obj.getMoney() ){
				updateSql = "update cts_work set rewardVerifyStatus=2 where id="+workId;
				this.getSession().createSQLQuery( updateSql ).executeUpdate();
				break;
			}
			if( obj.getWrittenOffmoney()!=null &&  obj.getWrittenOffmoney() == obj.getMoney() ){
				finish ++;
			}
		}
		if( list3!=null && list3.size()>0 && finish == list3.size() ){
			updateSql = "update cts_work set rewardVerifyStatus=3 where id="+workId;
			this.getSession().createSQLQuery( updateSql ).executeUpdate();
		}
		
		
		//更新应收核销状态
		finish = 0;
		for( WorkFee obj: list){
			if( obj.getWrittenOffmoney()!=null && obj.getWrittenOffmoney() >0 && obj.getWrittenOffmoney()<obj.getMoney() ){
				updateSql = "update cts_work set receiveVerifyStatus=2 where id="+workId;
				this.getSession().createSQLQuery( updateSql ).executeUpdate();
				break;
			}
			if( obj.getWrittenOffmoney()!=null &&  obj.getWrittenOffmoney() == obj.getMoney() ){
				finish ++;
			}
		}
		if( list!=null && list.size()>0 && finish == list.size() ){
			updateSql = "update cts_work set receiveVerifyStatus=3 where id="+workId;
			this.getSession().createSQLQuery( updateSql ).executeUpdate();
		}
		
		
		//更新应付核销状态
		finish = 0;
		for( WorkFee obj: list2){
			if( obj.getWrittenOffmoney()!=null && obj.getWrittenOffmoney() >0 && obj.getWrittenOffmoney()<obj.getMoney() ){
				updateSql = "update cts_work set payVerifyStatus=2 where id="+workId;
				this.getSession().createSQLQuery( updateSql ).executeUpdate();
				break;
			}
			if( obj.getWrittenOffmoney()!=null &&  obj.getWrittenOffmoney() == obj.getMoney() ){
				finish ++;
			}
		}
		if( list2!=null && list2.size()>0 && finish == list2.size() ){
			updateSql = "update cts_work set payVerifyStatus=3 where id="+workId;
			this.getSession().createSQLQuery( updateSql ).executeUpdate();
		}
		
	}

	public int getCountByStatus(Company company, Employee employee, User user, int status) {
		// TODO Auto-generated method stub
		return 0;
	}





}



























